// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System.Collections;
using System.IO;
using System.Data.Common;
using System.Data.OleDb;
using System.Net;
using Microsoft.Win32;
using net.windward.utils.ado.DB2;
using net.windward.utils.ado.Excel;
using net.windward.utils.ado.MySql;
using net.windward.utils.ado.Oracle;
using net.windward.utils.ado.SPList;
using net.windward.utils.ado.SqlServer;

// bugbug
// 1: get FK:PK relationships for columns.
// 2: type-safe table & column names
// 3: get schema owner for tables, views, stored procedures
// 3: unit test connecting to Oracle, DB2, MySql, & Access
// 4: get table/view/stored procedure/column descriptions.
// 4: get stored procedures variables (type & description)

namespace net.windward.utils.ado.OleDb
{
	/// <summary>
	/// This is similiar to DbProviderFactory and provides additional functionality for each provider. All properties to list
	/// if a provider can perform any task in it's child classes is provider here, not in the child classes. This
	/// class can return IWrServer objects for a given server/instance that exists on a computer. The ODBC/OleDb
	/// concept of a Provider (which is different from a DbProviderFactory) is handled as a property set in the
	/// WrProviderFactory class so all other cases do not need to implement a class for that layer.
	/// This is called WrVendor instead of WrProvider because it is different from DbProviderFactory and because
	/// we use the term Provider for the ODBC/OleDb providers.
	/// This is the class for the OleDb database.
	/// </summary>
	public class WrOleDbVendor : WrVendor
	{
		/// <summary>
		/// The DbProviderFactory class for this vendor
		/// </summary>
		public const string FactoryClass = "System.Data.OleDb";

		/// <summary>
		/// The suggested name for this vendor.
		/// </summary>
		public const string FactoryName = "OleDb";

		private string oledbProvider;

		/// <summary>
		/// Creates an OleDb vendor object.
		/// </summary>
		public WrOleDbVendor()
			: base(FactoryName, "The standard OleDb connector", FactoryClass)
		{
		}

		/// <summary>
		/// Creates an OleDb vendor object.
		/// </summary>
		/// <param name="name">The friendly name of the provider.</param>
		/// <param name="description">The description of this provider (optional).</param>
		/// <param name="providerClass">The class of the provider.</param>
		public WrOleDbVendor(string name, string description, string providerClass)
			: base(name, description, providerClass)
		{
		}

		#region Properties

		/// <summary>
		/// True if can enumerate running servers from this vendor. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateServers
		{
			get { return false; }
		}

		/// <summary>
		/// True if can enumerate databases on a server. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateDatabases
		{
			get { return true; }
		}

		/// <summary>
		/// True if can launch the administrator for this vendor. This will return true if it can find the admin
		/// program file. In that case the launch could still fail.
		/// </summary>
		public override bool CanLaunchAdministrator
		{
			get { return false; }
		}

		/// <summary>
		/// True if can run DDL scripts.
		/// </summary>
		public override bool CanRunScripts
		{
			get { return false; }
		}

		/// <summary>
		/// True if can use the WindowsIdentity of the caller eliminating the need to pass a username and password.
		/// </summary>
		public override bool CanUseTrustedConnection
		{
			get { return true; }
		}

		/// <summary>
		/// True if you need to select a provider for this vendor.
		/// </summary>
		public override bool HasProviders
		{
			get { return true; }
		}

		/// <summary>
		/// True if this vendor has the concept of databases in an installed copy of their product. This is false for Oracle (which
		/// does not have the concept of a database) and for databases like Access that are just a file - and the file is the "server."
		/// </summary>
		public override DATABASE_MODE DatabaseMode
		{
			get { return DATABASE_MODE.OPTIONAL; }
		}

		/// <summary>
		/// The name of the (OleDb/OleDb) providers for this (vendor) provider.
		/// </summary>
		/// <exception cref="WrDbException">Thrown if this (vendor) providers does not support (OleDb/OleDb) providers.</exception>
		public override string OdbcProvider
		{
			get { return oledbProvider; }
			set
			{
				oledbProvider = value;
				syntax = CalcSyntax(providerFactory, oledbProvider);
			}
		}

		///<summary>
		/// Create a syntax object if we can guess who for.
		///</summary>
		///<param name="provider">The connector provider.</param>
		///<param name="oledbProvider">The ODBC provider.</param>
		///<returns>The syntax. null if can't figure it out.</returns>
		public static IWrSyntax CalcSyntax(DbProviderFactory provider, string oledbProvider)
		{
			oledbProvider = oledbProvider.ToLower();
			if (oledbProvider.StartsWith("db2") || oledbProvider.EndsWith("db2"))
				return new WrDB2Syntax(provider);
			if (oledbProvider.StartsWith("mysql") || oledbProvider.EndsWith("mysql"))
				return new WrMySqlSyntax(provider);
			if (oledbProvider.StartsWith("ora") || oledbProvider.EndsWith("ora"))
				return new WrOracleSyntax(provider);
			if (oledbProvider.Contains("sqlncli") || oledbProvider.Contains("sqloledb"))
				return new WrSqlServerSyntax();
            if (oledbProvider.Equals("microsoft.jet.oledb.4.0 (*.xls, *.csv)") || oledbProvider.Equals("microsoft.ace.oledb.12.0 (*.xlsx, *.xlsb, *.xlsm)"))
                return new WrExcelSyntax(provider, oledbProvider);
            if(oledbProvider.Equals("microsoft.ace.oledb.12.0 (sharepoint list)"))
                return new WrSPListSyntax(provider, oledbProvider);
			return null;
		}

		#endregion

		#region WrVendor methods

		/// <summary>
		/// A connection string for a database on a server.
		/// </summary>
		/// <param name="server">The server to access (ex: localhost).</param>
		/// <param name="database">The database to connect to.</param>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="showPassword">True to include the password, false to add the password as *****.</param>
		/// <returns>The connection string for this database on this server.</returns>
		public override string ConnectionString(string server, string database, WrCredentials credentials, bool showPassword)
		{
			return ConnectionString(providerFactory, oledbProvider, server, database, credentials, showPassword);
		}

		internal static string ConnectionString(DbProviderFactory providerFactory, string oledbProvider, string server, string database, WrCredentials credentials, bool showPassword)
		{
			#region Excel as datasource case

			if ((oledbProvider != null) && ((oledbProvider.ToLower() == "microsoft.jet.oledb.4.0 (*.xls, *.csv)")
									|| (oledbProvider.ToLower() == "microsoft.ace.oledb.12.0 (*.xlsx, *.xlsb, *.xlsm)")))
			{
				if (string.IsNullOrEmpty(server))
					return "";

				int fileExtPos = server.LastIndexOf(".");
				string fileExtension;
				if (fileExtPos == -1)
				{
					fileExtension = "csv";
					if (!server.Trim().EndsWith("\\"))
						server += "\\";
				}
				else
					fileExtension = server.Substring(fileExtPos + 1);

				DbConnectionStringBuilder connStrBuilder = providerFactory.CreateConnectionStringBuilder();

				switch (oledbProvider.ToLower())
				{
					case "microsoft.jet.oledb.4.0 (*.xls, *.csv)":
						connStrBuilder.Add("Provider", "Microsoft.Jet.OLEDB.4.0");
						break;
					case "microsoft.ace.oledb.12.0 (*.xlsx, *.xlsb, *.xlsm)":
						connStrBuilder.Add("Provider", "Microsoft.ACE.OLEDB.12.0");
						break;
					default:
						return "";
				}

				switch (fileExtension)
				{
					case "xls":
						connStrBuilder.Add("Extended Properties", "Excel 8.0;HDR=Yes;IMEX=1");
						break;
					case "csv":
					case "txt":
						connStrBuilder.Add("Extended Properties", "text;HDR=Yes;FMT=Delimited");
						break;
					case "xlsx":
						connStrBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
						break;
					case "xlsb":
						connStrBuilder.Add("Extended Properties", "Excel 12.0;HDR=YES");
						break;
					case "xlsm":
						connStrBuilder.Add("Extended Properties", "Excel 12.0 Macro;HDR=YES");
						break;
					default:
						return "";
				}

				if (!string.IsNullOrEmpty(server))
					if ((fileExtension != "csv") && (fileExtension != "txt"))
						connStrBuilder.Add("Data Source", server);
					else
						connStrBuilder.Add("Data Source", Path.GetDirectoryName(server));

				return connStrBuilder.ConnectionString;
			}

			#endregion

			if ((oledbProvider != null) && oledbProvider.ToLower().Equals("microsoft.ace.oledb.12.0 (sharepoint list)"))
				return WrSPListVendor.ConnectionString(providerFactory, database, server, credentials, true);

			// Default OleDB behavior
			{
				DbConnectionStringBuilder connStrBuilder = providerFactory.CreateConnectionStringBuilder();
				if (!string.IsNullOrEmpty(oledbProvider))
					connStrBuilder.Add("Provider", oledbProvider);
				if (!string.IsNullOrEmpty(server))
				{
					connStrBuilder.Add("Data Source", server);
					IPAddress addr;
					if (IPAddress.TryParse(server, out addr))
						connStrBuilder.Add("Network Library", "DBMSSOCN");
				}
				if (!string.IsNullOrEmpty(database))
					connStrBuilder.Add("Initial Catalog", database);
				if (!credentials.UseWindowsIdentity)
				{
					if (!string.IsNullOrEmpty(credentials.Username))
						connStrBuilder.Add("User Id", credentials.Username);
					if (!string.IsNullOrEmpty(credentials.Password))
						connStrBuilder.Add("Password", showPassword ? credentials.Password : "*****");
				}
				return connStrBuilder.ConnectionString;
			}
		}

		/// <summary>
		/// Returns the parameters from a connection string making the best guess. Will return null for items it could not determine.
		/// </summary>
		/// <param name="connectionString">The connection string to parse.</param>
		/// <returns>The parameters.</returns>
		public override WrConnectionParams ConnectionParams(string connectionString)
		{


			if (string.IsNullOrEmpty(connectionString))
				return new WrConnectionParams();

			OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(connectionString);
			return new WrConnectionParams(builder.Provider, builder.DataSource, builder.FileName, builder["User ID"] as string, builder["Password"] as string);
		}

		/// <summary>
		/// Return all (OleDb/OleDb) providers for this (vendor) provider.
		/// </summary>
		/// <exception cref="WrDbException">Thrown if this (vendor) providers does not support (OleDb/OleDb) providers.</exception>
		/// <returns>The name of each provider.</returns>
		public override OdbcDriverInfo[] GetOdbcProviders()
		{
			ArrayList names = new ArrayList();
			using (RegistryKey keyCLSID = Registry.ClassesRoot.OpenSubKey("CLSID", false))
			{
				if (keyCLSID == null)
					return new OdbcDriverInfo[0];
				
				string[] keys = keyCLSID.GetSubKeyNames();
				for (int ind = 0; ind < keys.Length; ind++)
				{
					using (RegistryKey key = keyCLSID.OpenSubKey(keys[ind], false))
					{
						if (key != null)
						{
							using (RegistryKey subKey = key.OpenSubKey("OLE DB Provider", false))
							{
								if (subKey != null)
								{
									using (RegistryKey subKey2 = key.OpenSubKey("VersionIndependentProgID", false))
									{
										if (subKey2 != null)
											names.Add(new OdbcDriverInfo(subKey.GetValue(subKey.GetValueNames()[0]) as string,
											                             subKey2.GetValue(subKey2.GetValueNames()[0]) as string));
									}
								}
							}
						}
					}
				}
			}
			names.Sort();
			return (OdbcDriverInfo[])names.ToArray(typeof(OdbcDriverInfo));
		}

		#endregion

		#region DbProvider methods

		/// <summary>
		/// Returns a new instance of the provider's class that implements the WrCommand class.
		/// </summary>
		/// <returns>A new instance of WrCommand.</returns>
		public override WrCommand CreateCommand()
		{
			return new WrOleDbCommand(providerFactory, oledbProvider);
		}

		/// <summary>
		/// Create a server object.
		/// </summary>
		/// <param name="server">The name of the server the database is on.</param>
		public override IWrServer CreateServer(string server)
		{
			return new WrOleDbServer(providerFactory, oledbProvider, server);
		}

		#endregion

	}
}
